import pymysql


db_host = '127.0.0.1'
db_port = 3306
db_user = 'root'
db_password = 'root'
db_database = 'tfj'
db_charset = 'utf8mb4'

table1_name = 'goods_images_eval_o2'
table2_name = 'goods_images_eval_o1'
new_table_name = 'goods_images_eval_o'


def merge_tables():
    """将两个MySQL表合并到一个新表中"""
    connection = None
    try:
        # 连接到数据库
        connection = pymysql.connect(host=db_host,
                                     port=db_port,
                                     user=db_user,
                                     password=db_password,
                                     database=db_database,
                                     charset=db_charset)
        print("成功连接到数据库。")

        with connection.cursor() as cursor:
            # 1. 如果新表已存在，则删除它
            print(f"正在删除已存在的表 `{new_table_name}`...")
            cursor.execute(f"DROP TABLE IF EXISTS `{new_table_name}`")

            # 2. 根据 table1 的结构创建新表
            print(f"正在创建新表 `{new_table_name}`...")
            cursor.execute(f"CREATE TABLE `{new_table_name}` LIKE `{table1_name}`")

            # 3. 从 table1 插入数据
            print(f"正在从 `{table1_name}` 插入数据...")
            cursor.execute(f"INSERT INTO `{new_table_name}` SELECT * FROM `{table1_name}`")
            print(f"从 `{table1_name}` 插入了 {cursor.rowcount} 行。")

            # 4. 从 table2 插入数据
            print(f"正在从 `{table2_name}` 插入数据...")
            cursor.execute(f"INSERT INTO `{new_table_name}` SELECT * FROM `{table2_name}`")
            print(f"从 `{table2_name}` 插入了 {cursor.rowcount} 行。")

            # 5. 验证总行数
            cursor.execute(f"SELECT COUNT(*) FROM `{new_table_name}`")
            total_rows = cursor.fetchone()[0]
            print(f"`{new_table_name}` 表中的总行数: {total_rows}")

        # 提交事务
        connection.commit()
        print("数据表合并成功并已提交事务。")

    except pymysql.Error as e:
        print(f"数据库操作失败: {e}")
        if connection:
            connection.rollback()
            print("事务已回滚。")
    finally:
        # 关闭连接
        if connection:
            connection.close()
            print("数据库连接已关闭。")


if __name__ == '__main__':
    merge_tables()








